﻿using System;
using System.Data;
using System.Data.Common;
using System.Text;
using Besture.Common;
using Besture.Model;

namespace Besture.Dal
{
    public partial class RelatedAdvisoryDal : BaseDataAccess
    {

        public DataTable GetDataTableByUserId(int iUserId)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append(@"select tbh.HotelsName,tbra.* from TBRelatedAdvisory tbra 
                            join TBHotels tbh on tbra.HotelsID = tbh.ID 
                            where tbra.UserID=" + iUserId + " and tbra.IsDeleted=0 order by tbra.UpdateTime desc");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }



        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="id">主键值</param>
        /// <returns></returns>
        public RelatedAdvisoryModel GetModel(int id)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT * FROM TBRelatedAdvisory");
                selectSql.Append(" WHERE ");
                selectSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<RelatedAdvisoryModel>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 获取全部数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable()
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("  SELECT tbh.HotelsName,tbub.NickName," +
                                 " (select LoginName from TBAdminUser where ID=tbra.ReplyAdminUserID) ReplyAdminUser," +
                                 " tbra.* FROM TBRelatedAdvisory tbra left join TBHotels tbh on tbra.HotelsID = tbh.ID " +
                                 " left join TBUserBase tbub on tbra.UserID = tbub.ID order by tbra.Reply,tbra.UpdateTime desc ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(RelatedAdvisoryModel model)
        {
            var identityId = -1;
            try
            {

				
            string sqlString = string.Format(@"
                INSERT INTO TBRelatedAdvisory
                (
                        HotelsID
                        ,UserID
                        ,Content
                        ,Reply
                        ,ReplyAdminUserID
                        ,ReplyTime
                        ,SortID
                        ,IsDeleted
                        ,Created
                        ,CreateTime
                        ,Updated
                        ,UpdateTime
                )
                VALUES
                (
                        @HotelsID
                       ,@UserID
                       ,@Content
                       ,@Reply
                       ,@ReplyAdminUserID
                       ,@ReplyTime
                       ,@SortID
                       ,@IsDeleted
                       ,@Created
                       ,@CreateTime
                       ,@Updated
                       ,@UpdateTime
                )SELECT @@IDENTITY");

            DbCommand ldcDBCommand = base.GetCommand(sqlString);

            //参数设定
            SetParameter(ldcDBCommand, "@HotelsID", DbType.Int32, model.HotelsID);
            SetParameter(ldcDBCommand, "@UserID", DbType.Int32, model.UserID);
            SetParameter(ldcDBCommand, "@Content", DbType.String, model.Content);
            SetParameter(ldcDBCommand, "@Reply", DbType.String, model.Reply);
            SetParameter(ldcDBCommand, "@ReplyAdminUserID", DbType.Int32, model.ReplyAdminUserID);
            SetParameter(ldcDBCommand, "@ReplyTime", DbType.DateTime, model.ReplyTime);
            SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
            SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
            SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
            SetParameter(ldcDBCommand, "@CreateTime", DbType.DateTime, model.CreateTime);
            SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
            SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);



                identityId = base.ExecuteScalarInsert(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return identityId;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(RelatedAdvisoryModel model)
        {
            int iTmp = -1;
            try
            {
				
            string sqlString = string.Format(@"
                UPDATE TBRelatedAdvisory
                SET
                    HotelsID = @HotelsID
                    ,UserID = @UserID
                    ,Content = @Content
                    ,Reply = @Reply
                    ,ReplyAdminUserID = @ReplyAdminUserID
                    ,ReplyTime = @ReplyTime
                    ,SortID = @SortID
                    ,IsDeleted = @IsDeleted
                    ,Created = @Created
                    ,Updated = @Updated
                    ,UpdateTime = @UpdateTime
                WHERE
                    ID = @ID
            ");

            DbCommand ldcDBCommand = base.GetCommand(sqlString);
            //绑定SQL参数
            SetParameter(ldcDBCommand, "@ID", DbType.Int32, model.ID);
            SetParameter(ldcDBCommand, "@HotelsID", DbType.Int32, model.HotelsID);
            SetParameter(ldcDBCommand, "@UserID", DbType.Int32, model.UserID);
            SetParameter(ldcDBCommand, "@Content", DbType.String, model.Content);
            SetParameter(ldcDBCommand, "@Reply", DbType.String, model.Reply);
            SetParameter(ldcDBCommand, "@ReplyAdminUserID", DbType.Int32, model.ReplyAdminUserID);
            SetParameter(ldcDBCommand, "@ReplyTime", DbType.DateTime, model.ReplyTime);
            SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
            SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
            SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
            SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
            SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);


                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 单个删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("DELETE FROM TBRelatedAdvisory");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public int DeleteList(string ids)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("exec('DELETE FROM TBRelatedAdvisory");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID in ('+@ID+') ')");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.String, ids);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }
    }
}

